-1

Hello fellow programmers,

to describe my problem a bit better:

I've got 2 tables with a lot of columns. They have some of coloumns in common.

Table 1 is the Customer table with Customer ID, First Name, Last Name, Company, Street, Zipcode, City, category and others.

Table 2 is the Bookings table has First Name, Last Name, Company, Street, Zipcode and City, too. Table 2 has additionaly the Coloums Room_ID, Date, Customer_ID and others. Stupid thing is: The Customer_ID is empty at some entries.

Now I have to get the data of every Person from Costumer table that booked a room.

After some reasearch and after reading trough http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins and How to do an INNER JOIN on multiple columns I think that I need an Inner Join for this.

My problem is that the approach recommended in How to do an INNER JOIN on multiple columns doesn't reaaly fit to what I need, since I whant to join on 2 coloumns and not 2 times on the same soloumn.

This is my query so far: 

'SELECT tbl1.ID, tbl1.company, tbl1.title, tbl1.lastname, tbl1.fistname, tbl1.street, tbl1.city, tbl1.zipcode, tbl1.phone, tbl1.email, tbl1.category_ID, tbl1.Hotel_ID, tbl2.*
                 FROM customer AS tbl1
           INNER JOIN booking AS tbl2
                   ON 
                WHERE '.$searchterms.'
             GROUP BY tbl1.firstname, tbl1.lastname, tbl2.room, tbl2.date'

here is some pseudo-code of the way the join should look like:

'Select needed cloumns
                 FROM customer AS tbl1
           INNER JOIN booking AS tbl2 
                   ON tbl1.firstname=tbl2.firstname AND tbl1.lastname=tbl2.lastname
                WHERE '.$searchterms.'
             GROUP BY tbl1.firstname, tbl1.lastname, tbl2.room, tbl2.date'

What I need is a hint, an explantion or a link that helps me to come up with a solution and I'd be very thankfull. I've got some kind of blackout regarding explained problem. I hope that you can help me.

Community
  • 1
  • 1
TheMansn
  • 3
  • 3

2 Answers2

0
'SELECT ID, company, title, lastname, fistname, street, city, zipcode, phone, email,     category_ID, Hotel_ID
             FROM customer AS tbl1
       INNER JOIN booking AS tbl2
               ON tbl1.id=tbl2.id
            WHERE relevantcolumn='searchterms'
         GROUP BY tbl1.firstname, tbl1.lastname, tbl2.room, tbl2.date'
Mihai
  • 26,325
  • 7
  • 66
  • 81
  • after re-reading your answer 3 times I'm not sure if I understand your answer correctly. Can you explain further please? – TheMansn Aug 19 '13 at 10:56
  • SELECT columns from the customer table ,than give it an alias as tbl1 ,than join in with booking table a,give it an alias as tbl2 with the condition that somecolumn has the searchterms and than group it be what you need.A join needs a condition ,that is a column existent in both tables which has a communality between them. – Mihai Aug 19 '13 at 11:01
  • oh that is what you meant. I already knew that but thanks for your explanation :) the problem is that i need to take the data from 2 different coloumns (firstname and lastname) as one in order to compare the 2 tables – TheMansn Aug 19 '13 at 11:19
-1

Sorry if my answer is wrong, but I am not sure I got the question correctly, but anyway, I will try my best to answer this. the join objective, is to be able to query multiple related tables for data in both, and I think you know that already, but the point is, you need a linking zone between the two tables, which should be unique, in other words, the customerId in the second table(foreign key) should be the customer ID of the first (primary key), and then, after the linking is done, you can refine the search with conditions. The linking between the two tables is done with the "ON", but you seem to mistake it with the "where" clause. So, to say it shortly, 

SELECT tbl1.*, tbl2.* 
FROM tbl1 INNER JOIN tbl2 
ON tbl1.id=tbl2.Customer_ID 
WHERE WhateverConditions 
GROUP BY tbl1.firstname, tbl1.lastname, tbl2.room

I hope I answered what you need.

Labib Ismaiel
  • 1,240
  • 2
  • 11
  • 21
  • I think you understood my question quite well. the problem is that the customer_id in the booking table is missing for some entries and it is nearly impossible to find them out and insert them. this error is due to the guy who programmed the interface years ago before I was ordered to create a small expansion of an existing system. – TheMansn Aug 19 '13 at 11:00
  • apart from this question, my suggestion is to run a script to update the missing fields and filling the missing data, and making the field required in the table, so that such things don't happen in the future, then using this kind of query. As for updating the missing data, you can run a query on the available data to retrieve the customer_id for each one, then an update query for the other table. I don't know if it makes scene, but this is how I would maintain my data. – Labib Ismaiel Aug 19 '13 at 11:10
  • ok, I'll give it a try. so the script for the "data maintanace" would roughly be like: `step 1: get firstname, lastname, ID and adress from customer table step 2: compare firstname, lastname and adress with all entries from booking table step 3: if I find a match, I put in the ID from customer table into customer_id from booking table step 4: repeat 1 till 3 untill I#m trough all entries from customer table` – TheMansn Aug 19 '13 at 11:21
  • thank you for your help Labib Ismaiel :) after running a data maintanance script I tried your query and it gives me the data I need. Putting the right conditions into the `WHERE`-part shouldn't be such a problem. One litle thing about your query: it should be `INNER JOIN` instead of `INNERJOIN` but that's a small error that could happen to everyone. I thank you very much, you where a great help. – TheMansn Aug 20 '13 at 08:47
  • You're welcome, this is what we're here for, and thanks for notifying me about the mistake, I corrected it now, and good luck. – Labib Ismaiel Aug 20 '13 at 08:54