Select querry ...two tables!!

Hi,

What is the querry to achieve this?

I need to get the following fields from Sales table:

Item1, Item2, Item3

And the following fields from Cash table:

Item4, Item5, Item6

So, How can I write an SQL Querry for the above?

Regards.

Code:

---

Select Item1, Item2, Item3, Item4, Item5, Item6
From Sales
Inner Join Cash
  On Cash.SalesID = Sales.SalesID

---

This assumes there's a field (I've called it SalesID on the cash record that tellls it which sales record it applies to.

Quote:

---

Originally Posted by FunkyDexter

This assumes there's a field (I've called it SalesID on the cash record that tellls it which sales record it applies to.

---

Hi,

I will clear it for you. There are no ID fields in both tables and it is not a relational database both are completely individual. I will re-write it the question:

I need to get the following fields from Sales table:

ClientName .....> it is not connected with Cash table
SalesDate .....> it is not connected with Cash table
SalesAmount .....> it is not connected with Cash table

And the following fields from Cash table:

ClientName .....> it is not connected with Sales table
CashDate .....> it is not connected with Sales table
CashAmount .....> it is not connected with Sales table

So, How can I write an SQL Querry for the above?

That depends on what you want the output to look like. If you want
ClientName, SalesDate, SalesAmount
ClientName, SalesDate, SalesAmount
ClientName, SalesDate, SalesAmount
ClientName, CashDate, CashAmount
ClientName, CashDate, CashAmount
ClientName, CashDate, CashAmount

then

Code:

---

select ClientName as Client, SalesDate as Date, SalesAmount as Amount
From Sales
UNION
Select ClientName as Client, CashDate as Date, CashAmount as Amount

---

If you want:-
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount

Then you're going to need a field in both tables which links them - it looks a bit like you're using cilent name to do that but I'm not sure

Quote:

---

Originally Posted by FunkyDexter

If you want:-
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount
ClientName, SalesDate, SalesAmount, ClientName, CashDate, CashAmount

Then you're going to need a field in both tables which links them - it looks a bit like you're using cilent name to do that but I'm not sure

---

Yes, I need the above format.

What fields do i need? Please explain me in detail.

Seema

The problem is that you currently got no way of specifying which 'Cash' row you want printed alongside which 'Sales' Row. Normally you'd have a structure where the rows in one of the tables are children of the rows in the other table - ie they 'belong' to the rows in the other table.

eg. You might have a row in the sales table where John Smith bought goods to the value of £50 on 24/2/06. This might then relate to 2 records on the cash table, one for £20 on the 24/2/06 and one for £30 on the 24/3/06. In this case the two records on the Cash table 'belong' to the record on the sales table.

Unless there's some kind of relationship like that you can never get the output you're after because there's no way of specifying which cash rows to put alongside which sales rows. So the first thing you need to think about is how these two tables are related - if you can answer that you'll be most of the way there and we can tell you how to model the relationship with SQL.