Using joins in single query question

Question

Post reply

Using joins in single query question

Snowbat

Grasshopper

Points: 16
More actions
May 20, 2016 at 6:57 am

#327246

Hello everyone, I'm new to this forum. I used to learn SQL at school but it's quite hazy now since I havent used it in a long time since I started working.
Anyways, for work, I'm making a database of our customers. I have two tables:
tblCustomers
tblContacts
tblCustomers contains the customers and for the sake of simplicity, lets say it has these columns:
- Name
- Accountnumber
- ID (auto increment) to be sure each record can be uniquely identified
tblContacts contains the contact persons at the customers (for example, the CEO, the IT-man, the accountant for company X)
- Name
- Email
- CustomerID: this refers to the auto increment ID column in tblCustomers so we now which customer it belongs to
When I do a search for a customer, it must be possible to be able to extract all contacts that belong to it but we don't need to be able to search for a contact to see which customer it belongs to.
It's always Search customer --> Which contacts belong to him.
However, I'm not quite sure how to insert new records in the database properly. I have a php page with one single form. This php contains variables for each one of the fields of both tables
- customername
- contactname
- customeraccount
- contactemail
I remember how you do an insert.
INSERT INTO tblCustomers (Name, Accountnumber)
VALUES (customername, customeraccount);
and
INSERT INTO tblContacts (Name, Email)
VALUES (contactname, contactemail);
So far it's quite simple right?
However... the problem arises when I want to make sure the record in tblContacts is properly linked to it's corresponding record in tblCustomers. How can I give in the value CustomerID of the tblContacts table the value that was assigned to this customer when creating the record in tblCustomers at the same time?
I vaguely remember some stuff about joins, but there's so many different types... you don't have to give me the specific code, but if you can point me in the good direction, I'll use good ol' Google to do the rest 🙂
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

After the first insert you need to get the inserted value and use it in second insert query.

Search on SCOPE_IDENTITY() for more info.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Y.B. SSChampion Points: 11649 More actions · Answer 2

I think you are looking for this https://msdn.microsoft.com/en-CA/library/ms187342.aspx

This will return the last inserted identity value.

SELECT @@IDENTITY

You can then use that in a follow up insert statement.

CREATE TABLE #Customers (ID INT IDENTITY, Name VARCHAR(20), AccountNumber INT)

CREATE TABLE #Contacts (Name VARCHAR(20), Email VARCHAR(50), CustomerID INT)

DECLARE @IdentValue INT

INSERT INTO #Customers (Name, AccountNumber)

VALUES ('Someone', 123456)

SET @IdentValue = (SELECT @@IDENTITY)

INSERT INTO #Contacts (Name, Email, CustomerID)

VALUES('Someone', 'Someone@gmail.com', @IdentValue)

SELECT * FROM #Customers

SELECT * FROM #Contacts

DROP TABLE #Customers

DROP TABLE #Contacts

EDIT: I've included a very simple example

SELECT quote FROM brain WHERE original = 1
0 rows returned

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Just to be sure, because it's a common confusion. Are you using SQL Server?

In your post you mentioned autoincrement instead of identity (which is the word used in SQL Server). You also mentioned php, which usually is used with mySQL or other opensource RDBMS.

The reason for clarifying this is because the solutions posted here will work for MS SQL Server, but might not work for other systems. SQL is a standard, but it has many dialects that change from implementation to implementation.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Y.B. SSChampion Points: 11649 More actions · Answer 4

Luis Cazares (5/20/2016)
Just to be sure, because it's a common confusion. Are you using SQL Server?
In your post you mentioned autoincrement instead of identity (which is the word used in SQL Server). You also mentioned php, which usually is used with mySQL or other opensource RDBMS.
The reason for clarifying this is because the solutions posted here will work for MS SQL Server, but might not work for other systems. SQL is a standard, but it has many dialects that change from implementation to implementation.

Good observation Luis.

If you installed a LAMP stack or WordPress instance for example you will be running MySQL.

Run the following queries

SELECT @@version

OR (MySQL)

SELECT version();

SELECT quote FROM brain WHERE original = 1
0 rows returned

Snowbat Grasshopper Points: 16 More actions · Answer 5

My mistake, I failed to precise it's indeed MySQL.

I assumed that there's always equivalents in MySQL for general things like these. That's why I said I could Google it if I had an idea what to look for 🙂

Thanks for the suggestions. I'll try it out and report back.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

The only other thing I'd suggest, drop the tbl from your table names.