Selecting records based on ID

[eluser]kgill[/eluser]
That's why I said generally, your standard insert statement puts one row in, the ability to add multiple rows by adding a comma and the next set of data is a MySQL thing (and possibly PostgreSQL if I remember correctly).

[eluser]TheFuzzy0ne[/eluser]
Well I meant it the way I said it.

[eluser]RS71[/eluser]
Thanks guys

The UNION is pretty helpful. I have some questions though:

Is there a way to differentiate the rows based on table? So that in PHP I can separate the results?

I was mainly asking about the UNION of SELECTs because I thought it would be better performance wise. (the whole the less queries, the better idea) Would I see any benefits from combining these SELECTs? (especially considering I'd most likely have to use PHP to separate them later)

Also, weird thing but the union seems to be combining my fields.

My tables:

Code:

Books:
      ID
      UID
      books

Shows:
      ID
      UID
      shows

In the result, it combines all fields even books and shows into one field called 'books'. What happens in UNIONs with non similar fields?

[eluser]kgill[/eluser]
[quote author="RS71" date="1236817717"]Thanks guys

The UNION is pretty helpful. I have some questions though:

Is there a way to differentiate the rows based on table? So that in PHP I can separate the results?
[/quote]

Take a look at the example I gave again - that first column is a constant and differentiates what came from what table.

[quote author="RS71" date="1236817717"]
In the result, it combines all fields even books and shows into one field called 'books'. What happens in UNIONs with non similar fields?[/quote]

UNION only works when you have the same number of fields and the fields are the same types, what you're doing is combining the results of two queries into one result set. The only reason it called the field books is that was the first column name it found, if you had the shows query first it would have called them shows - use an alias for the column if you find it confusing. As for performance gains, you're making one less trip to the database but depending on what you're doing with the data in the view it may or may not make a difference.

[eluser]RS71[/eluser]
Interesting, thank you.

Perhaps an UNION is not necessarily what I need since the tables I'd be using don't have the same number of fields. If they did I'd probably have them in the same table and differentiate them via an extra field no?

How could I achieve the following?

http://i42.tinypic.com/ymkwo.jpg

It would be a mess but what about having my table structured as the result structure I made in the picture above? I could probably just do a

Code:

SELECT * FROM table WHERE UID = 1

I assume the query would be faster but it would be a mess since I'd be trying to combine three non related tables with about 10 fields each into one. That and I'd have to run a loop with an if statement wherever I used the results just to separate them.

What do you think?

[eluser]TheFuzzy0ne[/eluser]
[quote author="RS71" date=""]How could I achieve the following?

http://i42.tinypic.com/ymkwo.jpg[/quote]
With a JOIN.

[eluser]RS71[/eluser]
hah

Is there a join that returns nothing for fields not related to that row's table and doesn't exponentially increase the number of rows?

It feels like I am over complicating things hah. Considering I'd have lots of traffic, should I still pursue this?

[eluser]TheFuzzy0ne[/eluser]
It depends. Does one table have more rows than the other? If not, a simple natural JOIN will suffice. If you have more rows in one table than the other, you have the choice. You can either not display the "extra" rows, or you can display all of those rows, but where there's not a suitable matching row in the other table it will give you blank fields. Often this is desirable behaviour.

[eluser]RS71[/eluser]
The second behaviour, which type of join would that be?

I keep trying various joins in phpmyadmin but they're not giving me anything good to work with.

Is there a join that inserts nulls on fields that certain row's table doesn't have regardless of the left, right or wherever I join the tables?

[eluser]TheFuzzy0ne[/eluser]
If you want NULLS to show on both sides where no corresponding data exists, you will need a FULL OUTER JOIN.