Can I do this in one query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cobraroll98
Forum Newbie
Posts: 4
Joined: Wed Jul 18, 2007 12:56 am

Can I do this in one query?

Post by cobraroll98 »

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!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

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:

Post by nickvd »

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
Is the 'AS' keyword not required?
...
contacts AS c, tags AS t



Or am I learning something new today? ;)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

It's not required.
cobraroll98
Forum Newbie
Posts: 4
Joined: Wed Jul 18, 2007 12:56 am

Thanks!

Post by cobraroll98 »

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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

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.
Post Reply