Hello,
I have a query that pulls basic info:
select id,name, title, phone from contacts limit 30;
but for each row I want to display an unknown number of tags connected to the contact record.
select * from tags where contact.id = tags.contact_id;
The output I'm trying to get is:
id, name, title, phone
tag1, tag2, tag3, tag4, tag5, .....
id,name,title,phone
tag1, tag2, tag3, tag4, tag5, .....
I can get to the results above using seperate queries for each row to lookup the tags BUT this is slow
and I have a feeling I can do it in my initial query. Can I append an unknown number of items with a join clause?
Thanks!
Can I do this in one query?
Moderator: General Moderators
Like this?...
Code: Select all
select c.id, c.name, c.title, c.phone, t.* from contacts c, tags t where c.id=t.id limit 30
-
nickvd
- DevNet Resident
- Posts: 1027
- Joined: Thu Mar 10, 2005 5:27 pm
- Location: Southern Ontario
- Contact:
Is the 'AS' keyword not required?astions wrote:Like this?...
Code: Select all
select c.id, c.name, c.title, c.phone, t.* from contacts c, tags t where c.id=t.id limit 30
...
contacts AS c, tags AS t
Or am I learning something new today?
-
cobraroll98
- Forum Newbie
- Posts: 4
- Joined: Wed Jul 18, 2007 12:56 am
Thanks!
Your suggestion creates another row for each t.* item, is there a way to make it so that there is only 1 unique row
for the entire record? Ideally I'm trying to print out:
$txt = '<a href>'.$result['t.*1'].'</a>.<a href>'.$result['t.*2'].'</a><a href>'.$result['t.*2'].'</a>';
Can I use the mysql concat function to build a string into the existing query?
for the entire record? Ideally I'm trying to print out:
$txt = '<a href>'.$result['t.*1'].'</a>.<a href>'.$result['t.*2'].'</a><a href>'.$result['t.*2'].'</a>';
Can I use the mysql concat function to build a string into the existing query?
What astions showed you is an inner join. If you're not familiar with joins, do read up on that, it's basic to anything you do with two or more tables. In fact you very well may need to do something like a left join, since the inner join won't show you any records that are not in both tables.
But to answer your question about having a variable number of fields returned for each record, as far as I know, there's no way to do that with SQL. You would have to handle that in code that reads the returned data and formats the output.
But to answer your question about having a variable number of fields returned for each record, as far as I know, there's no way to do that with SQL. You would have to handle that in code that reads the returned data and formats the output.