Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

mysql join

I having some problem in joining two tables together one has the number in eg 1 2 3 4

the other table has id then some text eg id 1 text hello, id=2 text goodbye

SELECT * 
JOIN `ClientSource` ON `sourceID` = `ClientSource` ;

Open in new window

that what I tried with no luck
0
beridius
Asked:
beridius
  • 4
  • 4
  • 2
  • +3
2 Solutions
 
Kim WalkerWeb Programmer/TechnicianCommented:
You only have one table listed. It should be:

SELECT * FROM `table1` JOIN `table2` ON `table1.field1` = `table2.field2`;

Open in new window

0
 
tigin44Commented:
your quey misses some part.. it should look like this

SELECT *
FROM `table1` T
    JOIN `ClientSource` C ON T.`sourceID` = C.`ClientSource` ;  
0
 
25112Commented:
beridius I think your problem is related to convertion..

see this code:

-----------------------------------
declare @ClientSource table (Int_column int)
declare @ClientDestination table (string_column varchar(50))

insert into @ClientSource select 1 union select 2 union select 3
insert into @ClientDestination select '1 text hello' union select '2 text goodbye'


SELECT *
FROM @ClientSource CS
    JOIN @ClientDestination CD ON CS.Int_column = CD.string_column

-----------------------------------

It will give an error like

Conversion failed when converting the varchar value '1 text hello' to data type int.

You need to cast the datatype to avoid the error;

SELECT *
FROM @ClientSource CS
    JOIN @ClientDestination CD  ON cast(CS.Int_column as varchar(50))= CD.string_column

This will solve the problem
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LowfatspreadCommented:
which database system are you using ... they all have there own syntax styles...
0
 
beridiusAuthor Commented:
phpmyadmin (mysql)
0
 
LowfatspreadCommented:
so remove the ms sql server and database miscellaneous zones...

you query is mysql specific...

can you show us some example data for the two tables from your description of the problem
it isn't clear how the data is represented within the tables..

we also need to know the datatypes of the columns

0
 
beridiusAuthor Commented:
Table structure for table tbl_source
Column      Type      Null      Default
sourceID      int(11)      No      
sourceName      varchar(50)      Yes      NULL
Dumping data for table tbl_source
sourceID      sourceName

DATA


1      Website Browsing
2      Mailshot
3      Passing Or Local
4      Recommended


Table structure for table tbl_client
Column      Type      Null      Default
clientID      int(11)      No      
Client_level      int(10)      No      1
clientTitle      varchar(50)      Yes      NULL
ClientFirstname      varchar(50)      Yes      NULL
ClientSurname      varchar(50)      Yes      NULL
ClientAddress1      varchar(50)      Yes      NULL
ClientAddress2      varchar(50)      Yes      NULL
ClientAddress3      varchar(50)      Yes      NULL
ClientAddress4      varchar(50)      Yes      NULL
ClientAddress5      varchar(50)      Yes      NULL
ClientPostcode      varchar(50)      Yes      NULL
ClientCountry      varchar(255)      Yes      NULL
clientDeliveryTitle      varchar(50)      Yes      NULL
ClientDeliveryFirstname      varchar(50)      Yes      NULL
ClientDeliverySurname      varchar(50)      Yes      NULL
ClientDeliveryAddress1      varchar(50)      Yes      NULL
ClientDeliveryAddress2      varchar(50)      Yes      NULL
ClientDeliveryAddress3      varchar(50)      Yes      NULL
ClientDeliveryAddress4      varchar(50)      Yes      NULL
ClientDeliveryAddress5      varchar(50)      Yes      NULL
ClientDeliveryPostcode      varchar(50)      Yes      NULL
ClientDeliveryCountry      varchar(255)      Yes      NULL
ClientContact      varchar(255)      Yes      0
ClientSource      int(11)      Yes      0
ClientSource2      varchar(100)      Yes      NULL
ClientPhoneHome      varchar(15)      Yes      NULL
ClientPhoneWork      varchar(15)      Yes      NULL
ClientPhoneMobile      varchar(15)      Yes      NULL
ClientPhoneMobile2      varchar(15)      Yes      NULL
ClientFaxHome      varchar(15)      Yes      NULL
ClientFaxWork      varchar(15)      Yes      NULL
ClientEmail      varchar(50)      Yes      NULL
ClientMailingList      tinyint(1)      Yes      0
ClientInfoSentNotes      longtext      Yes      NULL
ClientProductNotes      longtext      Yes      NULL
ClientOrderContact      int(11)      Yes      0
ClientSamplesSent      longtext      Yes      NULL
ClientShowroomVisitDate      datetime      Yes      NULL
Designer      varchar(255)      Yes      NULL
Clientfolderdir      varchar(255)      Yes      NULL

0
 
Kim WalkerWeb Programmer/TechnicianCommented:
It looks like tbl_source has sourceID values of 1, 2, 3, and 4. Are there records in tbl_client with clientID values that match (1, 2, 3, or 4)? Are you getting an error when you run the query with the corrected syntax?
0
 
beridiusAuthor Commented:
I just need the syntax to join the tables together (relationship)
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
I think we're talking about two different things. I remember relationships from my old (very old) days of MS Access databases. I'm not even sure you can create relationships in MySQL. The example you provided in your initial comment is a select query that dynamically creates records in a temporary table where the fields in each record are joined from different tables and returns those records to you for further processing. Are you trying to get data from the MySQL database or are you trying to create a relationship between two tables in a MySQL database?
0
 
beridiusAuthor Commented:
The data was in ms access and I have moved it to mysql there were relationship between in ms access
I need away to replicate in mysql
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
I don't know that relationships have any usefulness in MySQL. I haven't used them in nearly 10 years. Perhaps another expert will comment further.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in MySQL (MyISAM engine), relationships (foreign keys) are irrelevant, because they are not enforced (they are in InnoDB tables...)
however, JOIN does not need or require the foreign keys.

possibly you continue to post more of the sample data?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now